

	****************************************************************************
	************************ Append survey data ********************************
	****************************************************************************
	clear all 
	set more off
	
	use "C:\Users\EAsker\Desktop\ABR\nels_88\RFNELS88_Clean_data\RFNELS88"
	append  using "C:\Users\EAsker\Desktop\ABR\els_02\RFELS02_Clean_data\RFELS02"
	append  using "C:\Users\EAsker\Desktop\ABR\hsls_09\RFHLS09_Clean_data\RFHLS09.dta"
	
	egen unique_sch_id=concat(sch_id survey)
	save "C:\Users\EAsker\Desktop\ABR\data\indv_data_all.dta"  ,replace
	
	****************************************************************************
	*********************** Finance Data ***************************************
	****************************************************************************
		clear all 
		set more off
		use "C:\Users\EAsker\Desktop\ABR\data\cleaned_combined_ccd.dta"
		
		sort ncesid dpblack80 dmedinc80 dpurban80 region dpcol80 year
		
		keep year fipst ncesid rtrev_pp rcexp_pp rtexp_pp rtcapital_pp ///
		rev_pp rlrev_pp region  dpcol80 dpurban80 dpblack80 dmedinc80  
        
		label var rtrev_pp "Revenue per-pupil"
		label var rtexp_pp "Expenditure per-pupil"
		label var rtcapital_pp "Capital Outlay per-pupil"
		label var rev_pp "State Aid per-pupil"
		label var rlrev_pp "Local revenue per-pupil"
		label var rcexp_pp "Current Expenditure"
		
		save "C:\Users\EAsker\Desktop\ABR\data\finance_87_12.dta", replace
	****************************************************************************
	clear all 
	set more off 
	set maxvar 15000
	
	use "C:\Users\EAsker\Desktop\ABR\data\finance_87_12.dta", clear 
	sort ncesid year 
	
	/* Baseline characteristics vary within school districts 
	(51 school districts). The following code fixes that.*/
	
	gen dup=1 if (dmedinc80[_n]~=dmedinc80[_n-1])&(ncesid[_n]==ncesid[_n-1])
	
	egen dpblack80_m=mean(dpblack80), by(ncesid) 
	egen dmedinc80_m=mean(dmedinc80), by(ncesid) 
	egen dpurban80_m=mean(dpurban80), by(ncesid) 
	egen dpcol80_m=mean(dpcol80), by(ncesid) 
	
	drop dpblack80 dmedinc80 dpurban80 dpcol80  
	
	rename (dpblack80_m dmedinc80_m dpurban80_m dpcol80_m) ///
	(dpblack80 dmedinc80 dpurban80 dpcol80 )
	
	keep ncesid year fipst rtexp_pp rcexp_pp rlrev_pp rtrev_pp rtcapital_pp ///
	rev_pp region dpblack80 dmedinc80 dpurban80 dpcol80     
	reshape wide rtexp_pp rcexp_pp rlrev_pp rtrev_pp rtcapital_pp rev_pp , ///
	i(ncesid fipst dpblack80 dmedinc80 dpurban80 dpcol80 )  j(year)
	

	// Generating Quartiles, Michigan's districts divided based on spending
	egen qrdinc90_sp=xtile(rcexp_pp1990), nq(4) by(fipst)
	egen qrdinc80_b=xtile(dmedinc80), nq(4) by(fipst)
	
	// Michigan Spending
	replace qrdinc80_b=qrdinc90_sp if fipst==26
	drop if qrdinc80_b==.
	gen qr80_1=1 if qrdinc80_b==1
	replace qr80_1=0 if qrdinc80_b~=1
	gen qr80_2=1 if qrdinc80_b==2
	replace qr80_2=0 if qrdinc80_b~=2
	gen qr80_3=1 if qrdinc80_b==3
	replace qr80_3=0 if qrdinc80_b~=3
	gen qr80_4=1 if qrdinc80_b==4
	replace qr80_4=0 if qrdinc80_b~=4
	
	gen stfips=fipst
	
	// Merging School Finance Events
	merge m:1 stfips using "C:\Users\EAsker\Desktop\ABR\data\finance_events.dta" 
	drop if _merge~=3
	drop _merge
	
	save "C:\Users\EAsker\Desktop\ABR\data\finance_all_final.dta",replace
	
	****************************************************************************
	************************* Final Data ***************************************
	****************************************************************************
	clear all 
	set more off 
	set maxvar 15000
	
	use "C:\Users\EAsker\Desktop\ABR\data\indv_data_all.dta"
	
	rename fips_state fipst
	
	// Expected year of graduation for each survey
	gen year=.
	replace year=1992 if survey==1988  
	replace year=2004 if survey==2002  
	replace year=2012 if survey==2009  
	
	sort  fipst ncesid
	
	* Merging finance data with individual-level survey data.
	
	merge m:1 ncesid  using "C:\Users\EAsker\Desktop\ABR\data\finance_all_final.dta"

	drop if _merge~=3
	drop _merge
	
	/* We calculate average per-pupil expenditure for each NELS-1988, ELS-2002, 
	and HLS-2009 using publicly available school finance data at the district 
	level. To calculate average expenditure for NELS-1988, we started from 1987, 
	because data from previous years come from different sources 
	and do not match perfectly. */
	
	//1.  Per-pupil total expenditure
	egen mean_rtexp_88_87=rowmean(rtexp_pp1987 rtexp_pp1988 rtexp_pp1989 ///
	rtexp_pp1990 rtexp_pp1991) if survey==1988
	
	egen mean_rtexp_02=rowmean(rtexp_pp1993 rtexp_pp1994 rtexp_pp1995 ///
	rtexp_pp1996 rtexp_pp1997 rtexp_pp1998 rtexp_pp1999 rtexp_pp2000 ///
	rtexp_pp2001 rtexp_pp2002 rtexp_pp2003 rtexp_pp2004) if survey==2002
	
	egen mean_rtexp_09=rowmean(rtexp_pp2001 rtexp_pp2002 rtexp_pp2003 ///
	rtexp_pp2004 rtexp_pp2005 rtexp_pp2006 rtexp_pp2007 rtexp_pp2008 ///
	rtexp_pp2009 rtexp_pp2010 rtexp_pp2011 rtexp_pp2012) if survey==2009

	gen mean_rtexp_87=.
	replace mean_rtexp_87=mean_rtexp_88_87 if survey==1988
	replace mean_rtexp_87=mean_rtexp_02 if survey==2002
	replace mean_rtexp_87=mean_rtexp_09 if survey==2009
	
	//2.  Per-pupil current expenditure
	egen mean_rcexp_88_87=rowmean(rcexp_pp1987 rcexp_pp1988 rcexp_pp1989 ///
	rcexp_pp1990 rcexp_pp1991) if survey==1988
	egen mean_rcexp_02=rowmean(rcexp_pp1993 rcexp_pp1994 rcexp_pp1995 ///
	rcexp_pp1996 rcexp_pp1997 rcexp_pp1998 rcexp_pp1999 rcexp_pp2000 ///
	rcexp_pp2001 rcexp_pp2002 rcexp_pp2003 rcexp_pp2004) if survey==2002
	
	egen mean_rcexp_09=rowmean(rcexp_pp2001 rcexp_pp2002 rcexp_pp2003 ///
	rcexp_pp2004 rcexp_pp2005 rcexp_pp2006 rcexp_pp2007 rcexp_pp2008 ///
	rcexp_pp2009 rcexp_pp2010 rcexp_pp2011 rcexp_pp2012) if survey==2009
	
	gen mean_rcexp_87=.
	replace mean_rcexp_87=mean_rcexp_88_87 if survey==1988
	replace mean_rcexp_87=mean_rcexp_02 if survey==2002
	replace mean_rcexp_87=mean_rcexp_09 if survey==2009
	
	//3. Revenue per-pupil
	egen mean_rtrev_88_87=rowmean(rtrev_pp1987 rtrev_pp1988 rtrev_pp1989 ///
	rtrev_pp1990 rtrev_pp1991) if survey==1988
	
	egen mean_rtrev_02=rowmean(rtrev_pp1993 rtrev_pp1994 rtrev_pp1995 ///
	rtrev_pp1996 rtrev_pp1997 rtrev_pp1998 rtrev_pp1999 rtrev_pp2000 ///
	rtrev_pp2001 rtrev_pp2002 rtrev_pp2003 rtrev_pp2004) if survey==2002
	
	egen mean_rtrev_09=rowmean(rtrev_pp2001 rtrev_pp2002 rtrev_pp2003 ///
	rtrev_pp2004 rtrev_pp2005 rtrev_pp2006 rtrev_pp2007 rtrev_pp2008 ///
	rtrev_pp2009 rtrev_pp2010 rtrev_pp2011 rtrev_pp2012) if survey==2009
	
	gen mean_rtrev_87=.
	replace mean_rtrev_87=mean_rtrev_88_87 if survey==1988
	replace mean_rtrev_87=mean_rtrev_02 if survey==2002
	replace mean_rtrev_87=mean_rtrev_09 if survey==2009

	
	//4.  State aid per-pupil
	egen mean_rev_pp_88_87=rowmean(rev_pp1987 rev_pp1988 rev_pp1989 ///
	rev_pp1990 rev_pp1991) if survey==1988
	
	egen mean_rev_pp_02=rowmean(rev_pp1993 rev_pp1994 rev_pp1995 rev_pp1996 ///
	rev_pp1997 rev_pp1998 rev_pp1999 rev_pp2000 rev_pp2001 rev_pp2002 ///
	rev_pp2003 rev_pp2004) if survey==2002
	
	egen mean_rev_pp_09=rowmean(rev_pp2001 rev_pp2002 rev_pp2003 rev_pp2004 ///
	rev_pp2005 rev_pp2006 rev_pp2007 rev_pp2008 rev_pp2009 rev_pp2010 ///
	rev_pp2011 rev_pp2012) if survey==2009
	
	gen mean_rev_87=.
	replace mean_rev_87=mean_rev_pp_88_87 if survey==1988
	replace mean_rev_87=mean_rev_pp_02    if survey==2002
	replace mean_rev_87=mean_rev_pp_09    if survey==2009
	
	// Keeping the variables of interest
	keep stu_id sch_id fipst stfips ncesid year fam_inc survey wave weight ///
	mot_hs fat_hs white black hispanic oth_race not_white female high_school ///
	post_scndry vlntry_hours vltnry_prtcptn register_vote dpblack80 dmedinc80 ///
	dpurban80 dpcol80 qrdinc80_b qr80_1 qr80_2 qr80_3 qr80_4  CSFR_year_BJH ///
	mean_rtexp_87 mean_rcexp_87 mean_rtrev_87 mean_rev_87                 
	
	label var mean_rtexp_87 "Mean Total Expenditure Per-Pupil"
	label var mean_rcexp_87 "Mean Current Expenditure Per-Pupil"
	label var mean_rtrev_87 "Mean Total Revenue Per-Pupil"
	label var mean_rev_87 "Mean State Aid Per-Pupil"
	
	// Defining Reform's dummy
	
	gen CSFR_BHJ=0
	replace CSFR_BHJ=1 if  (year-CSFR_year_BJH)>0&CSFR_year_BJH~=.
	
	// Defining years of exposure (+1 represents the year the reform was implemented)
	
	gen exposure_year_BHJ=year-(CSFR_year_BJH+1)
	gen exposure_year_trend_BHJ=exposure_year_BHJ
	replace exposure_year_trend_BHJ=0 if  exposure_year_BHJ<=0|exposure_year_BHJ==.
	replace exposure_year_BHJ=0 if CSFR_BHJ==0&exposure_year_BHJ==.
		
	// Defining exposure in categorical year ranges
	
	gen dummy_30_20=1 if exposure_year_BHJ>=-30&exposure_year_BHJ<=-20
	replace  dummy_30_20=0 if dummy_30_20==.
	
	gen dummy_20_10=1 if exposure_year_BHJ>=-19&exposure_year_BHJ<=-10
	replace  dummy_20_10=0 if dummy_20_10==.
	
	gen dummy_10_1=1 if exposure_year_BHJ>=-9&exposure_year_BHJ<=-1
	replace  dummy_10_1=0 if dummy_10_1==.
	
	*********************************
	gen dummy_10=1 if exposure_year_BHJ>=-30&exposure_year_BHJ<=-10
	replace  dummy_10=0 if dummy_10==.
	
	gen dummy_6=1 if exposure_year_BHJ>=-10&exposure_year_BHJ<=-6
	replace  dummy_6=0 if dummy_6==.
	
	gen dummy_5=1 if exposure_year_BHJ>=-5&exposure_year_BHJ<=-1
	replace  dummy_5=0 if dummy_5==.
	
	// Interaction, reform and quartile
	gen  qr1_sfr_BHJ=CSFR_BHJ*qr80_1
	gen  qr2_sfr_BHJ=CSFR_BHJ*qr80_2
	gen  qr3_sfr_BHJ=CSFR_BHJ*qr80_3
	gen  qr4_sfr_BHJ=CSFR_BHJ*qr80_4

	//	Interaction, reform, quartile and 0<years of epsorue
	
	gen qr1_sfr_exp_trend_BHJ=qr1_sfr_BHJ*exposure_year_trend_BHJ 
		
	//	Interaction, trend analysis
	gen qr1_sfr_exp_BHJ=qr1_sfr_BHJ*exposure_year_BHJ
			
	**** Fixed Effects
	
	egen statexsurvey_quartile=concat(fipst survey)
	destring statexsurvey_quartile, replace
	label var statexsurvey_quartile "State by Survey FE"
		
	egen quartilexstate=concat(fipst qrdinc80_b)
	destring quartilexstate, replace
	label var quartilexstate "State by Quartile FE"
		
	egen quartilexsurvey=concat(survey qrdinc80_b)
	destring quartilexsurvey, replace
	label var quartilexsurvey "Quartile by Survey FE"
	
	save "C:\Users\EAsker\Desktop\ABR\data\ABR_final_data", replace
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
